﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DataLib.DBUtility;//Please add references
namespace DataLib.DAL
{
	/// <summary>
	/// 数据访问类:Project
	/// </summary>
	public partial class Project
	{
		public Project()
		{}
        #region  BasicMethod

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ProjectID", "Project");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ProjectID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Project");
            strSql.Append(" where ProjectID=@ProjectID");
            SqlParameter[] parameters = {
					new SqlParameter("@ProjectID", SqlDbType.Int,4)
			};
            parameters[0].Value = ProjectID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(DataLib.Model.Project model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Project(");
            strSql.Append("ProjectNum,ProjectName,CustomerID,CustomerNum,CreateDatetime,CreateOper,OperDate,Remark,belongAddress,CJOName,PrintName,LabName)");
            strSql.Append(" values (");
            strSql.Append("@ProjectNum,@ProjectName,@CustomerID,@CustomerNum,@CreateDatetime,@CreateOper,@OperDate,@Remark,@belongAddress,@CJOName,@PrintName,@LabName)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@ProjectNum", SqlDbType.NVarChar,50),
					new SqlParameter("@ProjectName", SqlDbType.NVarChar,50),
					new SqlParameter("@CustomerID", SqlDbType.Int,4),
					new SqlParameter("@CustomerNum", SqlDbType.NVarChar,50),
					new SqlParameter("@CreateDatetime", SqlDbType.DateTime),
					new SqlParameter("@CreateOper", SqlDbType.NVarChar,50),
					new SqlParameter("@OperDate", SqlDbType.DateTime),
					new SqlParameter("@Remark", SqlDbType.NVarChar,200),
					new SqlParameter("@belongAddress", SqlDbType.NVarChar,50),
                    new SqlParameter("@CJOName",SqlDbType.VarChar,200),
                    new SqlParameter("@PrintName",SqlDbType.VarChar,200),
                    new SqlParameter("@LabName",SqlDbType.VarChar,200)};
            parameters[0].Value = model.ProjectNum;
            parameters[1].Value = model.ProjectName;
            parameters[2].Value = model.CustomerID;
            parameters[3].Value = model.CustomerNum;
            parameters[4].Value = model.CreateDatetime;
            parameters[5].Value = model.CreateOper;
            parameters[6].Value = model.OperDate;
            parameters[7].Value = model.Remark;
            parameters[8].Value = model.belongAddress;
            parameters[9].Value = model.cjoname;
            parameters[10].Value = model.printname;
            parameters[11].Value = model.labname;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(DataLib.Model.Project model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Project set ");
            strSql.Append("ProjectNum=@ProjectNum,");
            strSql.Append("ProjectName=@ProjectName,");
            strSql.Append("CustomerID=@CustomerID,");
            strSql.Append("CustomerNum=@CustomerNum,");
            strSql.Append("CreateDatetime=@CreateDatetime,");
            strSql.Append("CreateOper=@CreateOper,");
            strSql.Append("OperDate=@OperDate,");
            strSql.Append("Remark=@Remark,");
            strSql.Append("CJOName=@CJOName,");
            strSql.Append("PrintName=@PrintName,");
            strSql.Append("LabName=@LabName,");
            strSql.Append("belongAddress=@belongAddress");
            strSql.Append(" where ProjectID=@ProjectID");
            SqlParameter[] parameters = {
					new SqlParameter("@ProjectNum", SqlDbType.NVarChar,50),
					new SqlParameter("@ProjectName", SqlDbType.NVarChar,50),
					new SqlParameter("@CustomerID", SqlDbType.Int,4),
					new SqlParameter("@CustomerNum", SqlDbType.NVarChar,50),
					new SqlParameter("@CreateDatetime", SqlDbType.DateTime),
					new SqlParameter("@CreateOper", SqlDbType.NVarChar,50),
					new SqlParameter("@OperDate", SqlDbType.DateTime),
					new SqlParameter("@Remark", SqlDbType.NVarChar,200),
					new SqlParameter("@belongAddress", SqlDbType.NVarChar,50),
                    new SqlParameter("@CJOName",SqlDbType.VarChar,200),
                    new SqlParameter("@PrintName",SqlDbType.VarChar,200),
                    new SqlParameter("@LabName",SqlDbType.VarChar,200),
					new SqlParameter("@ProjectID", SqlDbType.Int,4)};
            parameters[0].Value = model.ProjectNum;
            parameters[1].Value = model.ProjectName;
            parameters[2].Value = model.CustomerID;
            parameters[3].Value = model.CustomerNum;
            parameters[4].Value = model.CreateDatetime;
            parameters[5].Value = model.CreateOper;
            parameters[6].Value = model.OperDate;
            parameters[7].Value = model.Remark;
            parameters[8].Value = model.belongAddress;
            parameters[9].Value = model.cjoname;
            parameters[10].Value = model.printname;
            parameters[11].Value = model.labname;
            parameters[12].Value = model.ProjectID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ProjectID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Project ");
            strSql.Append(" where ProjectID=@ProjectID");
            SqlParameter[] parameters = {
					new SqlParameter("@ProjectID", SqlDbType.Int,4)
			};
            parameters[0].Value = ProjectID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string ProjectIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Project ");
            strSql.Append(" where ProjectID in (" + ProjectIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataLib.Model.Project GetModel(int ProjectID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ProjectID,ProjectNum,ProjectName,CustomerID,CustomerNum,CreateDatetime,CreateOper,OperDate,Remark,belongAddress,companyname,CJOName,PrintName,LabName from Project p,Cumtomer c ");
            strSql.Append(" where ProjectID=@ProjectID and  p.CustomerID=c.id");
            SqlParameter[] parameters = {
					new SqlParameter("@ProjectID", SqlDbType.Int,4)
			};
            parameters[0].Value = ProjectID;

            DataLib.Model.Project model = new DataLib.Model.Project();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataLib.Model.Project DataRowToModel(DataRow row)
        {
            DataLib.Model.Project model = new DataLib.Model.Project();
            if (row != null)
            {
                if (row["ProjectID"] != null && row["ProjectID"].ToString() != "")
                {
                    model.ProjectID = int.Parse(row["ProjectID"].ToString());
                }
                if (row["ProjectNum"] != null)
                {
                    model.ProjectNum = row["ProjectNum"].ToString();
                }
                if (row["ProjectName"] != null)
                {
                    model.ProjectName = row["ProjectName"].ToString();
                }
                if (row["CustomerID"] != null && row["CustomerID"].ToString() != "")
                {
                    model.CustomerID = int.Parse(row["CustomerID"].ToString());
                }
                if (row["CustomerNum"] != null)
                {
                    model.CustomerNum = row["CustomerNum"].ToString();
                }
                if (row["CreateDatetime"] != null && row["CreateDatetime"].ToString() != "")
                {
                    model.CreateDatetime = DateTime.Parse(row["CreateDatetime"].ToString());
                }
                if (row["CreateOper"] != null)
                {
                    model.CreateOper = row["CreateOper"].ToString();
                }
                if (row["OperDate"] != null && row["OperDate"].ToString() != "")
                {
                    model.OperDate = DateTime.Parse(row["OperDate"].ToString());
                }
                if (row["Remark"] != null)
                {
                    model.Remark = row["Remark"].ToString();
                }
                if (row["belongAddress"] != null)
                {
                    model.belongAddress = row["belongAddress"].ToString();
                }
                if (row["companyname"] != null)
                {
                    model.CustomerName = row["companyname"].ToString();
                }
                if (row["CJOName"] != null)
                {
                    model.cjoname = row["CJOName"].ToString();
                }
                if (row["PrintName"] != null)
                {
                    model.printname = row["PrintName"].ToString();
                }
                if (row["LabName"] != null)
                {
                    model.labname = row["LabName"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ProjectID,ProjectNum,ProjectName,CustomerID,CustomerNum,CreateDatetime,CreateOper,OperDate,Remark,belongAddress,cjoname,printname,labname ");
            strSql.Append(" FROM Project ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ProjectID,ProjectNum,ProjectName,CustomerID,CustomerNum,CreateDatetime,CreateOper,OperDate,Remark,belongAddress ");
            strSql.Append(" FROM Project ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Project ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.ProjectID desc");
            }
            strSql.Append(")AS Row, T.*  from Project T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "Project";
            parameters[1].Value = "ProjectID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
		#region  ExtensionMethod
        /// <summary>
		/// 得到一个对象实体
		/// </summary>
		public DataLib.Model.Project GetCustomerTop(int CustomerID)
		{
			
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select  top 1 ProjectID,ProjectNum,ProjectName,CustomerID,CustomerNum,CreateDatetime,CreateOper,OperDate,Remark,belongAddress,companyname,p.CJOName,p.LabName,p.PrintName ");
            strSql.Append(" from Project p,Cumtomer c ");
            strSql.Append(" where p.CustomerID=c.id and p.CustomerID=@CustomerID order by ProjectNum desc ");
			SqlParameter[] parameters = {
					new SqlParameter("@CustomerID", SqlDbType.Int,4)
			};
            parameters[0].Value = CustomerID;

			DataLib.Model.Project model=new DataLib.Model.Project();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				return DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
				return null;
			}
		}
        /// <summary>
        /// 批量更新项目名称
        /// </summary>
        /// <param name="projectname">需要更新的数据</param>
        /// <param name="tablename">需要更新的表名</param>
        /// <param name="strwhere">更新条件</param>
        /// <param name="columnName">需要更新的列名</param>
        public void updatelist(string dataname,string tablename,string strwhere,string columnName)
        {
            string strSql = string.Format("update {0} set {3} = '{1}' {2}", tablename, dataname, strwhere, columnName);
            DbHelperSQL.ExecuteSql(strSql);
        }

        /// <summary>
        /// 取客户名称
        /// <param name="proid"></param>
        /// <param name="type">1 CJO 2为打印单名称 3 打印标签名称</param>
        /// </summary>
        public string GetCusNameByProid(int proid,int type)
        {
          
            StringBuilder strSql = new StringBuilder();
                strSql.Append("select  top 1 ");
            if (type == 1)
            {
                strSql.Append("CJOName");
            }
            else if (type == 2)
            {
                strSql.Append("PrintName");
            }
            else
            {
                strSql.Append("LabName");
            }
            strSql.Append(" where ProjectID=@ProjectID");

            SqlParameter[] parameters = {
					new SqlParameter("@ProjectID", SqlDbType.Int,4)
			};
            parameters[0].Value = proid;

            DataLib.Model.Project model = new DataLib.Model.Project();
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];
            if (dt.Rows.Count > 0&&dt.Rows[0][0]!=null)
            {
                return dt.Rows[0][0].ToString();
            }
            else
            {
                return null;
            }
        }
		#endregion  ExtensionMethod
	}
}

